my_air_conditioner = 0
my_washing_machine = 1
my_television = 2
my_heater = 3
my_computer = 4
my_phone = 5
my_camera = 6
my_shoes = 7
my_sweater = 8 #卫衣
my_loose_coat = 9 #外套
my_trousers = 10

def purchase(var1, var2, var3):
    sql = '''insert into order_list
                values({var1}, '{var2}', {var3})
            '''.format(var1=var1, var2=var2, var3=var3)
    return sql

def update_appliances(var1, var2):
    sql = '''update appliances
                set sales_volume = sales_volume + 1, stock = stock - 1
                where category_id = {var1} and category_name = '{var2}'
            '''.format(var1=var1, var2=var2)
    return sql

def update_digital(var1, var2):
    sql = '''update digital
                set sales_volume = sales_volume + 1,stock = stock - 1
                where category_id = {var1} and category_name = '{var2}'
            '''.format(var1=var1, var2=var2)
    return sql

def update_shoes_clothes(var1, var2):
    sql = '''update shoes_clothes
                set sales_volume = sales_volume + 1,stock = stock - 1
                where category_id = {var1} and item_name = '{var2}'
            '''.format(var1=var1, var2=var2)
    return sql

#空调

def air_cond_none():
    #无条件查询
    sql = '''select air_cond.category_name, prices, sales_volume, stock, energy_effiency_ratio, appliances.category_id, appliances.category_name
                from appliances, air_cond
                where appliances.category_id = air_cond.category_id
        '''
    return sql

def air_cond_p(var):
    #查询空调价格
    sql = '''select air_cond.category_name, prices, sales_volume, stock, energy_effiency_ratio, appliances.category_id, appliances.category_name
                from appliances, air_cond
                where appliances.category_id = air_cond.category_id and prices < {var}
               ''' .format(var=var)
    return sql

def air_cond_b(var):
    #查询空调品牌
    sql = '''select air_cond.category_name, prices, sales_volume, stock, energy_effiency_ratio, appliances.category_id, appliances.category_name
                from appliances, air_cond
                where appliances.category_id = air_cond.category_id and appliances.brand_name = '{var}'
            '''.format(var=var)
    # print(var)
    return sql

def air_cond_r(var):
    #查询空调能效比
    sql = '''select air_cond.category_name, prices, sales_volume, stock, energy_effiency_ratio, appliances.category_id, appliances.category_name
                from appliances, air_cond
                where appliances.category_id = air_cond.category_id and air_cond.energy_effiency_ratio > {var}
            '''.format(var=var)
    return sql

def air_cond_pb(var1, var2):
    #查询品牌和价格
    sql = '''select air_cond.category_name, prices, sales_volume, stock, energy_effiency_ratio, appliances.category_id, appliances.category_name
                from appliances, air_cond
                where appliances.category_id = air_cond.category_id and prices < {var1} and appliances.brand_name = '{var2}'
            '''.format(var1=var1, var2=var2)
    return sql

def air_cond_pr(var1, var2):
    #查询价格和能效比
    sql = '''select air_cond.category_name, prices, sales_volume, stock, energy_effiency_ratio, appliances.category_id, appliances.category_name
                from appliances, air_cond
                where appliances.category_id = air_cond.category_id and prices < {var1} and air_cond.energy_effiency_ratio > '{var2}'
            '''.format(var1=var1, var2=var2)
    return sql

def air_cond_br(var1, var2):
    #查询品牌和能效比
    sql = '''select air_cond.category_name, prices, sales_volume, stock, energy_effiency_ratio, appliances.category_id, appliances.category_name
                from appliances, air_cond
                where appliances.category_id = air_cond.category_id and appliances.brand_name = '{var1}' and air_cond.energy_effiency_ratio > '{var2}'
            '''.format(var1=var1, var2=var2)
    return sql

def air_cond_bpr(var1, var2, var3):
    sql = '''select air_cond.category_name, prices, sales_volume, stock, energy_effiency_ratio, appliances.category_id, appliances.category_name
                from appliances, air_cond
                where appliances.category_id = air_cond.category_id and prices < {var1} and appliances.brand_name = '{var2}' and air_cond.energy_effiency_ratio > '{var3}'
            '''.format(var1=var1, var2=var2, var3=var3)
    return sql






def washing_machine_none():
    #无条件查询
    sql = '''select washing_machine.category_name, prices, water_consumption, volume, weight, appliances.category_id, appliances.category_name
                from appliances, washing_machine
                where appliances.category_id = washing_machine.category_id
            ''' 
    return sql

def washing_machine_p(var):
    #查询价格
    sql = '''select washing_machine.category_name, prices, water_consumption, volume, weight, appliances.category_id, appliances.category_name
                from appliances, washing_machine
                where appliances.category_id = washing_machine.category_id and prices < {var}
               ''' .format(var=var)
    return sql

def washing_machine_b(var):
    #查询品牌
    sql = '''select washing_machine.category_name, prices, water_consumption, volume, weight, appliances.category_id, appliances.category_name
                from appliances, washing_machine
                where appliances.category_id = washing_machine.category_id and appliances.brand_name = '{var}'
               ''' .format(var=var)
    return sql

def washing_machine_v(var):
    sql = '''select washing_machine.category_name, prices, water_consumption, volume, weight, appliances.category_id, appliances.category_name
                from appliances, washing_machine
                where appliances.category_id = washing_machine.category_id and washing_machine.volume = {var}
            '''.format(var=var)
    return sql

def washing_machine_pb(var1, var2):
    sql = '''select washing_machine.category_name, prices, water_consumption, volume, weight, appliances.category_id, appliances.category_name
                from appliances, washing_machine
                where appliances.category_id = washing_machine.category_id and prices < {var1} and appliances.brand_name = '{var2}'
            '''.format(var1=var1, var2=var2)
    return sql

def washing_machine_pv(var1, var2):
    sql = '''select washing_machine.category_name, prices, water_consumption, volume, weight, appliances.category_id, appliances.category_name
                from appliances, washing_machine
                where appliances.category_id = washing_machine.category_id and prices < {var1} and washing_machine.volume = {var2}
            '''.format(var1=var1, var2=var2)
    return sql

def washing_machine_bv(var1, var2):
    sql = '''select washing_machine.category_name, prices, water_consumption, volume, weight, appliances.category_id, appliances.category_name
                from appliances, washing_machine
                where appliances.category_id = washing_machine.category_id and appliances.brand_name = '{var1}' and washing_machine.volume = {var2}
            '''.format(var1=var1, var2=var2)
    return sql

def washing_machine_pbv(var1, var2, var3):
    sql = '''select washing_machine.category_name, prices, water_consumption, volume, weight, appliances.category_id, appliances.category_name
                from appliances, washing_machine
                where appliances.category_id = washing_machine.category_id and prices < {var1} and appliances.brand_name = '{var2}' and washing_machine.volume = {var3}
            '''.format(var1=var1, var2=var2, var3=var3)
    return sql



def television_none():
    sql = '''select television.category_name, prices, stock, screen_size, net, appliances.category_id, appliances.category_name
                from appliances, television
                where appliances.category_id = television.category_id
            '''
    return sql

def television_p(var):
    sql = '''select television.category_name, prices, stock, screen_size, net, appliances.category_id, appliances.category_name
                from appliances, television
                where appliances.category_id = television.category_id and prices < {var}
            '''.format(var=var)
    return sql

def television_b(var):
    sql = '''select television.category_name, prices, stock, screen_size, net, appliances.category_id, appliances.category_name
                from appliances, television
                where appliances.category_id = television.category_id and appliances.brand_name = '{var}'
            '''.format(var=var)
    return sql

def television_s(var):
    sql = '''select television.category_name, prices, stock, screen_size, net, appliances.category_id, appliances.category_name
                from appliances, television
                where appliances.category_id = television.category_id and television.screen_size = {var}
            '''.format(var=var)
    return sql

def television_pb(var1, var2):
    sql = '''select television.category_name, prices, stock, screen_size, net, appliances.category_id, appliances.category_name
                from appliances, television
                where appliances.category_id = television.category_id and prices < {var1} and appliances.brand_name = '{var2}'
            '''.format(var1=var1, var2=var2)
    return sql

def television_ps(var1, var2):
    sql = '''select television.category_name, prices, stock, screen_size, net, appliances.category_id, appliances.category_name
                from appliances, television
                where appliances.category_id = television.category_id and prices < {var1} and television.screen_size = {var2}
            '''.format(var1=var1, var2=var2)
    return sql

def television_bs(var1, var2):
    sql = '''select television.category_name, prices, stock, screen_size, net, appliances.category_id, appliances.category_name
                from appliances, television
                where appliances.category_id = television.category_id and appliances.brand_name = '{var1}' and television.screen_size = {var2}
            '''.format(var1=var1, var2=var2)
    return sql

def television_pbs(var1, var2, var3):
    sql = '''select television.category_name, prices, stock, screen_size, net, appliances.category_id, appliances.category_name
                from appliances, television
                where appliances.category_id = television.category_id and prices < {var1} and appliances.brand_name = '{var2}' and television.screen_size = {var3}
            '''.format(var1=var1, var2=var2, var3=var3)
    return sql



def heater_none():
    sql = '''select heater.category_name, prices, stock, hotwater_production, heatingmode, appliances.category_id, appliances.category_name
                from appliances, heater
                where appliances.category_id = heater.category_id
            '''
    return sql

def heater_p(var):
    sql = '''select heater.category_name, prices, stock, hotwater_production, heatingmode, appliances.category_id, appliances.category_name
                from appliances, heater
                where appliances.category_id = heater.category_id and prices < {var}
            '''.format(var=var)
    return sql

def heater_b(var):
    sql = '''select heater.category_name, prices, stock, hotwater_production, heatingmode, appliances.category_id, appliances.category_name
                from appliances, heater
                where appliances.category_id = heater.category_id and appliances.brand_name = '{var}'
            '''.format(var=var)
    return sql

def heater_h(var):
    #加热方式索引
    sql = '''select heater.category_name, prices, stock, hotwater_production, heatingmode, appliances.category_id, appliances.category_name
                from appliances, heater
                where appliances.category_id = heater.category_id and heater.heatingmode = '{var}'
            '''.format(var=var)
    return sql

def heater_pb(var1, var2):
    sql = '''select heater.category_name, prices, stock, hotwater_production, heatingmode, appliances.category_id, appliances.category_name
                from appliances, heater
                where appliances.category_id = heater.category_id and prices < {var1} and appliances.brand_name = '{var2}'
    '''.format(var1=var1, var2=var2)
    return sql

def heater_ph(var1, var2):
    sql = '''select heater.category_name, prices, stock, hotwater_production, heatingmode, appliances.category_id, appliances.category_name
                from appliances, heater
                where appliances.category_id = heater.category_id and prices < {var1} and heater.heatingmode = '{var2}'
    '''.format(var1=var1, var2=var2)
    return sql

def heater_bh(var1, var2):
    sql = '''select heater.category_name, prices, stock, hotwater_production, heatingmode, appliances.category_id, appliances.category_name
                from appliances, heater
                where appliances.category_id = heater.category_id and appliances.brand_name = '{var1}' and heater.heatingmode = '{var2}'
    '''.format(var1=var1, var2=var2)
    return sql

def heater_pbh(var1, var2, var3):
    sql = '''select heater.category_name, prices, stock, hotwater_production, heatingmode, appliances.category_id, appliances.category_name
                from appliances, heater
                where appliances.category_id = heater.category_id and prices < {var1} and appliances.brand_name = '{var2}' and heater.heatingmode = '{var3}'
    '''.format(var1=var1, var2=var2, var3=var3)
    return sql



def computer_none():
    sql = '''select computer.category_name, prices, stock, cpu, hard_disk, memory, digital.category_id, digital.category_name
                from digital, computer
                where digital.category_id = computer.category_id
            '''
    return sql


def computer_p(var):
    sql = '''select computer.category_name, prices, stock, cpu, hard_disk, memory, digital.category_id, digital.category_name
                from digital, computer
                where digital.category_id = computer.category_id and prices < {var}
            '''.format(var=var)
    return sql

def computer_b(var):
    sql = '''select computer.category_name, prices, stock, cpu, hard_disk, memory, digital.category_id, digital.category_name
                from digital, computer
                where digital.category_id = computer.category_id and digital.brand_name = '{var}'
            '''.format(var=var)
    return sql

def computer_pb(var1, var2):
    sql = '''select computer.category_name, prices, stock, cpu, hard_disk, memory, digital.category_id, digital.category_name
                from digital, computer
                where digital.category_id = computer.category_id and prices < {var1} and digital.brand_name = '{var2}'
            '''.format(var1=var1, var2=var2)
    return sql



def phone_none():
    sql = '''select phone.category_name, prices, stock, cpu, memory, camera_pixel, digital.category_id, digital.category_name
                from digital, phone
                where digital.category_id = phone.category_id
            '''
    return sql

def phone_p(var):
    sql = '''select phone.category_name, prices, stock, cpu, memory, camera_pixel, digital.category_id, digital.category_name
                from digital, phone
                where digital.category_id = phone.category_id and prices < {var}
            '''.format(var=var)
    return sql

def phone_b(var):
    sql = '''select phone.category_name, prices, stock, cpu, memory, camera_pixel, digital.category_id, digital.category_name
                from digital, phone
                where digital.category_id = phone.category_id and digital.brand_name = '{var}'
            '''.format(var=var)
    return sql

def phone_pb(var1, var2):
    sql = '''select phone.category_name, prices, stock, cpu, memory, camera_pixel, digital.category_id, digital.category_name
                from digital, phone
                where digital.category_id = phone.category_id and prices < {var1} and digital.brand_name = '{var2}'
            '''.format(var1=var1, var2=var2)
    return sql



def camera_none():
    sql = '''select camera.category_name, prices, stock, pixel, weight, digital.category_id, digital.category_name
                from digital, camera
                where digital.category_id = camera.category_id
            '''
    return sql

def camera_p(var):
    sql = '''select camera.category_name, prices, stock, pixel, weight, digital.category_id, digital.category_name
                from digital, camera
                where digital.category_id = camera.category_id and prices < {var}
            '''.format(var=var)
    return sql

def camera_b(var):
    sql = '''select camera.category_name, prices, stock, pixel, weight, digital.category_id, digital.category_name
                from digital, camera
                where digital.category_id = camera.category_id and digital.brand_name = '{var}'
            '''.format(var=var)
    return sql

def camera_pb(var1, var2):
    sql = '''select camera.category_name, prices, stock, pixel, weight, digital.category_id, digital.category_name
                from digital, camera
                where digital.category_id = camera.category_id and prices < {var1} and digital.brand_name = '{var2}'
            '''.format(var1=var1, var2=var2)
    return sql



def shoes_none():
    sql = '''select category_name, prices, stock, sales_volume, brand_name, category_id, item_name
                from shoes_clothes
                where item_name = '鞋子'
            '''
    return sql

def shoes_p(var):
    sql = '''select category_name, prices, stock, sales_volume, brand_name, category_id, item_name
                from shoes_clothes
                where item_name = '鞋子' and prices < {var}
            '''.format(var=var)
    return sql

def shoes_b(var):
    sql = '''select category_name, prices, stock, sales_volume, brand_name, category_id, item_name
                from shoes_clothes
                where item_name = '鞋子' and brand_name = '{var}'
            '''.format(var=var)
    return sql

def shoes_pb(var1, var2):
    sql = '''select category_name, prices, stock, sales_volume, brand_name, category_id, item_name
                from shoes_clothes
                where item_name = '鞋子' and prices < {var1} and brand_name = '{var2}'
            '''.format(var1=var1, var2=var2)
    return sql



def sweater_none():
    sql = '''select category_name, prices, stock, sales_volume, brand_name, category_id, item_name
                from shoes_clothes
                where item_name = '卫衣'
            '''
    return sql

def sweater_p(var):
    sql = '''select category_name, prices, stock, sales_volume, brand_name, category_id, item_name
                from shoes_clothes
                where item_name = '卫衣' and prices < {var}
            '''.format(var=var)
    return sql

def sweater_b(var):
    sql = '''select category_name, prices, stock, sales_volume, brand_name, category_id, item_name
                from shoes_clothes
                where item_name = '卫衣' and brand_name = '{var}'
            '''.format(var=var)
    return sql

def sweater_pb(var1, var2):
    sql = '''select category_name, prices, stock, sales_volume, brand_name, category_id, item_name
                from shoes_clothes
                where item_name = '卫衣' and prices < {var1} and brand_name = '{var2}'
            '''.format(var1=var1, var2=var2)
    return sql




def loosecoat_none():
    sql = '''select category_name, prices, stock, sales_volume, brand_name, category_id, item_name
                from shoes_clothes
                where item_name = '外套'
            '''
    return sql

def loosecoat_p(var):
    sql = '''select category_name, prices, stock, sales_volume, brand_name, category_id, item_name
                from shoes_clothes
                where item_name = '外套' and prices < {var}
            '''.format(var=var)
    return sql

def loosecoat_b(var):
    sql = '''select category_name, prices, stock, sales_volume, brand_name, category_id, item_name
                from shoes_clothes
                where item_name = '外套' and brand_name = '{var}'
            '''.format(var=var)
    return sql

def loosecoat_pb(var1, var2):
    sql = '''select category_name, prices, stock, sales_volume, brand_name, category_id, item_name
                from shoes_clothes
                where item_name = '外套' and prices < {var1} and brand_name = '{var2}'
            '''.format(var1=var1, var2=var2)
    return sql



def trousers_none():
    sql = '''select category_name, prices, stock, sales_volume, brand_name, category_id, item_name
                from shoes_clothes
                where item_name = '裤子'
            '''
    return sql

def trousers_p(var):
    sql = '''select category_name, prices, stock, sales_volume, brand_name, category_id, item_name
                from shoes_clothes
                where item_name = '裤子' and prices < {var}
            '''.format(var=var)
    return sql

def trousers_b(var):
    sql = '''select category_name, prices, stock, sales_volume, brand_name, category_id, item_name
                from shoes_clothes
                where item_name = '裤子' and brand_name = '{var}'
            '''.format(var=var)
    return sql

def trousers_pb(var1, var2):
    sql = '''select category_name, prices, stock, sales_volume, brand_name, category_id, item_name
                from shoes_clothes
                where item_name = '裤子' and prices < {var1} and brand_name = '{var2}'
            '''.format(var1=var1, var2=var2)
    return sql



def my_orderlist(var):
    sql = '''select all_goods.category_name
                from all_goods, order_list
                where user_id = {var} and order_list.category_id = all_goods.category_id and order_list.category_name = all_goods.item_name
            '''.format(var=var)
    return sql


def user_information():
    sql = '''select * from user_imformation'''
    return sql

def orderList():
    sql = '''select user_id, order_list.category_name, all_goods.category_name
                from order_list, all_goods
                where order_list.category_id = all_goods.category_id and order_list.category_name = all_goods.item_name
            '''
    return sql